
Imports System
Imports System.Collections
Imports System.Data.SqlClient
Imports System.Diagnostics
Imports _2bMatesSite.Common

Namespace _2bMatesSite.BO
	''' <summary>
	''' Summary description for SpecialPage.
	''' </summary>
	Public Class SpecialPage
		Public ID As Integer

		Protected name As String
		Protected membersName As String
		Protected contentPartName As String
		Protected contentColumnName1 As String
		Protected contentColumnName2 As String
		Protected contentColumnName3 As String
		Protected articlePartName As String

		Public Sub New()
			Init(-1, "")
		End Sub

		Public Sub New(ByVal ID As Integer, ByVal Name As String)
			Init(ID, Name)
		End Sub

		Public Sub New(ByVal dr As SqlDataReader)
			LoadFromReader(dr)
		End Sub


		Public Property Name() As String
			Get
				Return name
			End Get

			Set
				name = value
			End Set
		End Property

		Public Property MembersName() As String
			Get
				Return membersName
			End Get

			Set
				membersName = value
			End Set
		End Property

		Public Property ContentPartName() As String
			Get
				Return contentPartName
			End Get

			Set
				contentPartName = value
			End Set
		End Property

		Public Property ContentColumnName1() As String
			Get
				Return contentColumnName1
			End Get

			Set
				contentColumnName1 = value
			End Set
		End Property

		Public Property ContentColumnName2() As String
			Get
				Return contentColumnName2
			End Get

			Set
				contentColumnName2 = value
			End Set
		End Property

		Public Property ContentColumnName3() As String
			Get
				Return contentColumnName3
			End Get

			Set
				contentColumnName3 = value
			End Set
		End Property

		Public Property ArticlePartName() As String
			Get
				Return articlePartName
			End Get

			Set
				articlePartName = value
			End Set
		End Property

		Public Sub Save()
			If ID <= 0 Then
				DoInsert()
			Else
				DoUpdate()
			End If
		End Sub

		Public Shared Function GetLastMonth(ByVal pageType As Integer) As DateTime
			Dim sql As String
			Dim tempDate As DateTime = DateTime.Now

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()
			sql = "SELECT TOP 1 * FROM NewsInterviews WHERE SpecialPageID = @pageType ORDER BY [Date] Desc"
			' Build the news list
			Dim cmdSelect As New SqlCommand(sql)
			cmdSelect.Connection = conn

			cmdSelect.Parameters.Add("@pageType", pageType)

			Dim dr As SqlDataReader = cmdSelect.ExecuteReader()
			' Read the news array
			If dr.Read() Then
				Dim ri As New ArticleItem(dr)
				tempDate = ri.ArticleDate
			End If
			conn.Close()

			Return tempDate
		End Function

		Private Sub DoInsert()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()
			Dim tran As SqlTransaction = conn.BeginTransaction()

			Try
				' Insert the special page
				Dim cmd As New SqlCommand(GetInsertSQL())
				cmd.Connection = conn
				cmd.Transaction = tran

				PopulateCommand(cmd)

				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)

				' Reload the special page
				DoRefresh(tran)

				tran.Commit()
			Catch e As Exception
				tran.Rollback()
				Throw e
			Finally
				conn.Close()
			End Try
		End Sub

		Protected Sub DoUpdate()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand(GetUpdateSQL())
				cmd.Connection = conn

				PopulateCommand(cmd)

				cmd.Parameters.Add("@ID", ID)

				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)
			Finally
				conn.Close()
			End Try
		End Sub

		Public Sub DoDelete()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				' Delete the special page
				Dim cmd As New SqlCommand(GetDeleteSQL())
				cmd.Connection = conn

				cmd.Parameters.Add("@ID", ID)

				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)
			Finally
				conn.Close()
			End Try
		End Sub

		Private Sub Init(ByVal ID As Integer, ByVal name As String)
			Me.ID = ID
			Me.name = name
			Me.membersName = ""
			Me.contentPartName = ""
			Me.contentColumnName1 = ""
			Me.contentColumnName2 = ""
			Me.contentColumnName3 = ""
			Me.articlePartName = ""
		End Sub

		Private Shared Function GetInsertSQL() As String
			Dim sql As String = "INSERT INTO SpecialPages" + Environment.NewLine
			sql += "( Name, MembersName, ContentPartName, ContentColumnName1, ContentColumnName2, ContentColumnName3, ArticlePartName )" + Environment.NewLine
			sql += "VALUES" + Environment.NewLine
			sql += "( @Name, @MembersName, @ContentPartName, @ContentColumnName1, @ContentColumnName2, @ContentColumnName3, @ArticlePartName )" + Environment.NewLine
			Return sql
		End Function

		Private Shared Function GetUpdateSQL() As String
			Dim sql As String = "UPDATE SpecialPages SET" + Environment.NewLine
			sql += "" & Chr(9) & "Name=@Name," + Environment.NewLine
			sql += "" & Chr(9) & "MembersName=@MembersName," + Environment.NewLine
			sql += "" & Chr(9) & "ContentPartName=@ContentPartName," + Environment.NewLine
			sql += "" & Chr(9) & "ContentColumnName1=@ContentColumnName1," + Environment.NewLine
			sql += "" & Chr(9) & "ContentColumnName2=@ContentColumnName2," + Environment.NewLine
			sql += "" & Chr(9) & "ContentColumnName3=@ContentColumnName3," + Environment.NewLine
			sql += "" & Chr(9) & "ArticlePartName=@ArticlePartName" + Environment.NewLine
			sql += "WHERE" + Environment.NewLine
			sql += "" & Chr(9) & "ID=@ID" + Environment.NewLine
			Return sql
		End Function

		Private Shared Function GetDeleteSQL() As String
			Dim sql As String = "DELETE FROM SpecialPages" + Environment.NewLine
			sql += "WHERE" + Environment.NewLine
			sql += "" & Chr(9) & "ID=@ID" + Environment.NewLine
			Return sql
		End Function

		Private Sub PopulateCommand(ByVal cmd As SqlCommand)
			DBManager.SetNZValue(cmd, "@Name", name)
			DBManager.SetNZValue(cmd, "@MembersName", membersName)
			cmd.Parameters.Add("@ContentPartName", contentPartName)
			cmd.Parameters.Add("@ContentColumnName1", contentColumnName1)
			cmd.Parameters.Add("@ContentColumnName2", contentColumnName2)
			cmd.Parameters.Add("@ContentColumnName3", contentColumnName3)
			cmd.Parameters.Add("@ArticlePartName", articlePartName)
		End Sub

		Protected Sub DoRefresh(ByVal sqlTrans As SqlTransaction)
			Dim conn As SqlConnection = Nothing
			Dim ownConnection As Boolean = False

			If sqlTrans = Nothing Then
				conn = DBManager.GetUnmanagedConnection()
				ownConnection = True
			Else
				conn = sqlTrans.Connection
			End If

			Try
				Dim cmd As New SqlCommand("SELECT * FROM SpecialPages WHERE Name = @Name")
				cmd.Parameters.Add("@Name", name)

				cmd.Connection = conn
				If Not ownConnection Then
					cmd.Transaction = sqlTrans
				End If

				Dim dr As SqlDataReader = cmd.ExecuteReader()

				Dim found As Boolean = dr.Read()
				Debug.Assert(found)
				LoadFromReader(dr)

				dr.Close()
			Finally
				If ownConnection Then
					conn.Close()
				End If
			End Try
		End Sub

		Protected Sub DoRefresh()
			DoRefresh(Nothing)
		End Sub

		Protected Sub LoadFromReader(ByVal dr As SqlDataReader)
			ID = dr.GetInt32(dr.GetOrdinal("ID"))

			name = DBManager.GetNZString(dr, "Name")

			membersName = DBManager.GetNZString(dr, "MembersName")

			contentPartName = DBManager.GetNZString(dr, "ContentPartName")

			contentColumnName1 = DBManager.GetNZString(dr, "ContentColumnName1")

			contentColumnName2 = DBManager.GetNZString(dr, "ContentColumnName2")

			contentColumnName3 = DBManager.GetNZString(dr, "ContentColumnName3")

			articlePartName = DBManager.GetNZString(dr, "ArticlePartName")

		End Sub

		Public Shared Function GetArticleItems(ByVal pageType As Integer, ByVal type As Integer, ByVal period As DateTime, ByVal numItems As Integer, ByVal usrID As Integer, ByVal grpID As Integer) As ArrayList
			Dim articleItems As New ArrayList()
			Dim sql As String

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				' for last month always show top N items, for all other months all items for the month
				sql = "SELECT"
				If numItems > 0 Then
					sql += " TOP " + numItems
				End If
				' Build the news list

				sql += " * FROM NewsInterviews WHERE SpecialPageID = @pageType AND Type = @type"
				If usrID > 0 Then
					sql += "  AND UID=@UID"
				End If
				If grpID > 0 Then
					sql += "  AND GroupID=@GroupID"
				End If
				If period <> DateTime.MinValue Then
					sql += " AND [Date] >= @period AND [Date] <= @periodTo "
					'sql += " AND [Date] >= @periodFrom AND [Date] <= @period ";
				End If
				sql += "  ORDER BY [Date] Desc"

				Dim cmdSelect As New SqlCommand(sql)
				cmdSelect.Connection = conn

				cmdSelect.Parameters.Add("@pageType", pageType)
				cmdSelect.Parameters.Add("@type", Convert.ToInt32(type))
				If usrID > 0 Then
					cmdSelect.Parameters.Add("@UID", usrID)
				End If
				If grpID > 0 Then
					cmdSelect.Parameters.Add("@GroupID", grpID)
				End If
				If period <> DateTime.MinValue Then
					'cmdSelect.Parameters.Add( "@periodFrom", 
					'	Common.DateUtilities.GetStartOfQuarter( period.Year, Common.DateUtilities.GetQuarter((Common.Month)period.Month )) );
					cmdSelect.Parameters.Add("@period", period)
					Dim endP As DateTime = Common.DateUtilities.GetEndOfQuarter(period.Year, Common.DateUtilities.GetQuarter(DirectCast(period.Month, Common.Month)))
					cmdSelect.Parameters.Add("@periodTo", endP)
				End If

				Dim dr As SqlDataReader = cmdSelect.ExecuteReader()
				' Read the news array
				While dr.Read()
					Dim ri As New ArticleItem(dr)
					articleItems.Add(ri)
				End While

				dr.Close()
			Catch generatedExceptionName As Exception
				Throw
			Finally
				conn.Close()
			End Try

			Return articleItems
		End Function


		Public Shared Function GetContentItems(ByVal pageType As Integer, ByVal period As DateTime, ByVal numItems As Integer, ByVal usrID As Integer, ByVal grpID As Integer) As ArrayList
			Dim sql As String
			Dim contentItems As New ArrayList()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				' for last month always show top N items, for all other months all items for the month
				sql = "SELECT"

				If numItems > 0 Then
					sql += " TOP " + numItems
				End If

				sql += " * FROM SpecialPageContent WHERE SpecialPageID = @pageType"
				If period <> DateTime.MinValue Then
					sql += " AND [Date] >= @period AND [Date] <= @periodTo "
					'sql += " AND [Date] >= @periodFrom AND [Date] <= @period ";
				End If
				If usrID > 0 Then
					sql += "  AND UID=@UID"
				End If
				If grpID > 0 Then
					sql += "  AND GroupID=@GroupID"
				End If
				sql += "  ORDER BY [Date] Desc"

				Dim cmdSelect As New SqlCommand(sql)
				cmdSelect.Connection = conn
				cmdSelect.Parameters.Add("@pageType", pageType)
				If usrID > 0 Then
					cmdSelect.Parameters.Add("@UID", usrID)
				End If
				If grpID > 0 Then
					cmdSelect.Parameters.Add("@GroupID", grpID)
				End If
				If period <> DateTime.MinValue Then
					'cmdSelect.Parameters.Add( "@periodFrom", 
					'	Common.DateUtilities.GetStartOfQuarter( period.Year, Common.DateUtilities.GetQuarter((Common.Month)period.Month )) );
					cmdSelect.Parameters.Add("@period", period)
					Dim endP As DateTime = Common.DateUtilities.GetEndOfQuarter(period.Year, Common.DateUtilities.GetQuarter(DirectCast(period.Month, Common.Month)))
					cmdSelect.Parameters.Add("@periodTo", endP)
				End If


				Dim dr As SqlDataReader = cmdSelect.ExecuteReader()

				' Read the news array
				While dr.Read()
					Dim li As New ContentItem(dr)
					contentItems.Add(li)
				End While

				dr.Close()
			Catch generatedExceptionName As Exception
				Throw
			Finally
				conn.Close()
			End Try

			Return contentItems
		End Function

		Public Shared Function GetSpecialUsers(ByVal pageType As Integer) As ArrayList
			Dim specialUsers As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				' Build the special users list
				Dim cmdSelect As New SqlCommand("SELECT * FROM vSpecialUsers WHERE SpecialPageID = @pageType")
				cmdSelect.Connection = conn
				cmdSelect.Parameters.Add("@pageType", pageType)

				Dim dr As SqlDataReader = cmdSelect.ExecuteReader()
				Dim ordID As Integer = dr.GetOrdinal("UID")
				Dim ordName As Integer = dr.GetOrdinal("Name")
				Dim ordLastName As Integer = dr.GetOrdinal("LastName")

				' Read the special users array
				While dr.Read()
					Dim usr As New SpecialUser(dr.GetInt32(ordID), dr.GetString(ordName), dr.GetString(ordLastName))
					specialUsers.Add(usr)
				End While

				dr.Close()
			Catch generatedExceptionName As Exception
				Throw
			Finally
				conn.Close()
			End Try

			Return specialUsers
		End Function

		Public Shared Function GetSpecialGroups(ByVal pageType As Integer) As ArrayList
			Dim specialGroups As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				' Build the special group list
				Dim cmdSelect As New SqlCommand("SELECT * FROM vSpecialGroups WHERE SpecialPageID = @pageType")
				cmdSelect.Connection = conn
				cmdSelect.Parameters.Add("@pageType", pageType)

				Dim dr As SqlDataReader = cmdSelect.ExecuteReader()
				Dim ordID As Integer = dr.GetOrdinal("GroupID")
				Dim ordName As Integer = dr.GetOrdinal("Name")

				' Read the special group array
				While dr.Read()
					Dim grp As New SpecialGroup(dr.GetInt32(ordID), dr.GetString(ordName))
					specialGroups.Add(grp)
				End While

				dr.Close()
			Catch generatedExceptionName As Exception
				Throw
			Finally
				conn.Close()
			End Try

			Return specialGroups
		End Function
	End Class
End Namespace






